#-----------------------------------------------------------------------------
# Name:        sql.py
# Purpose:     SQL- Interface for reppy
#
# Author:      wbauer <werner_bauer@aon.at>
#
# Created:     2005/01/30
# RCS-ID:      $Id: sql.py $
# Copyright:   (c) 2005
# Licence:     LGPL
#-----------------------------------------------------------------------------

import error

try:
	import MySQLdb
except:
	pass
try:
	import pgdb
except:
	pass


from sys import exit
import string
# connect to db
class DB:
# interface for Database Class
	field_types = {}
	def __init__(self):
		pass
	def get_error(self):
		''' Returns Error Message
		'''
		pass

	def get_fields(self):
		''' returns Dictionary of TableColumnName:TableColumnType
		'''
		pass

	def get_tables(self):
		''' returns array of tableNames
		'''
		pass

	def get_numrows(self):
		''' returns number of rows of select
		'''
		pass

	def next_row(self):
		''' fetches the next row of resultset and returns an array of values
		'''
		pass

	def get_fieldlen(self):
		''' returns a dictionary of field_name: field_length
		'''
		pass


class MySQL(DB):
	field_types = { 0:"DECIMAL",1:"TINY",2:"SHORT",3:"LONG",4:"FLOAT",5:"DOUBLE",6:"NULL",10:"DATE",11:"TIME",12:"DATETIME",
			253:"CHAR",
			254:"VARCHAR"}
	felder = []
	def __init__(self, host, user,passwd,db,table= None,sql=None, sort = None,master = None,
			masterlink = "", detaillink = ""):
	   	''' Connection to Mysql database (Paramters: host, user, passwd, db)
		returns Object or None. If None ist returned, an error occurred, which can be read with get_error()
		'''
		try:
			import MySQLdb
		except:
			error.errorPrint('ESQL001',None)  # no MySQLDB
		self.db_handle = None
        	try:
			self.db_handle = MySQLdb.connect(host, user, passwd, db)
        	except MySQLdb.Error:
			error.errorPrint('ESQL002',(host,user,db))
		if self.db_handle == None:
			error.errorPrint('ESQL003',None)
		# what tables are in database
		self._tables()
		if table != None and sql != None:
			error.errorPrint('ESQL004',(table,sql))
		if table != None:
			sqlCommand = "select * from %s " % (table)
			if master != None:
                		if masterlink == "" or detaillink == "":
					error.errorPrint('ESQL005',None)
				sqlCommandAdd = ",%s where %s.%s = %s.%s " %  (master, master, masterlink, table, detaillink)
				#sqlCommandAdd = ","
				sqlCommand = sqlCommand + sqlCommandAdd
			if sort != None :
            			sqlCommand = sqlCommand + " order by %s.%s" % (table, sort)
			self._select(sqlCommand)
			self._fields()


        	elif sql != None:
			self._select(sql)
            		self._fields()




	def _select(self, select_command):
		''' executes the select_command on the given database handle
		    returns true if ok, false otherwise
		'''

		try:
			self.cursor = self.db_handle.cursor()
			self.cursor.execute(select_command)
		except MySQLdb.Error:
			error.errorPrint('ESQL006',(select_command))

	def _fields(self):
		''' returns [(columnname,type),(columname,type) ...]
		'''

		self.felder = self.cursor.description
		r_felder = {}
		l_felder = {}
		for feld in self.felder:
			#f = (feld[0],self.field_types[feld[1]])
			r_felder[feld[0]] = self.field_types[feld[1]]
			l_felder[feld[0]] = feld[3]
		self.fields = r_felder
		self.field_len = l_felder

	def _tables(self):
		''' builds empty self.tables is done via SQL request with Statement SHOW TABLES
		'''
		self.tables = []



	def get_error(self):
		''' Returns Error Message
		'''
		return self.Error

	def get_fields(self):
		''' returns Dictionary of TableColumnName:TableColumnType
		'''
		return self.fields
	def get_tables(self):
		'''  returns array of tableNames
		'''
		return self.tables

	def get_fieldlen(self):
		''' returns Dictionary of TableColumnName:TableColumnSize
		'''
		return self.field_len

	def get_numrows(self):
		''' returns number of rows of select
		'''
		return int(self.cursor.rowcount)

	def next_row(self):
		''' fetches the next row of resultset and returns an array of values
		'''
		res = self.cursor.fetchone()
		if res == None:  # last record fetched
			self.db_handle.close()
			return None
		result = {}
		index = 0
		field_names= []
		for f in self.felder:
			field_names.append(f[0])
		for i in field_names:  # build dictionary columnName:value
			result[i]=res[index]
			index = index + 1
		return result

class CSV(DB):
	""" presents methods for working with Excel CSV Files
	"""
	def __init__(self, table, sql=None,master=None, masterlink=None,detaillink=None, sort=None):
		try:
			import csv
		except:
			error.errorPrint('ESQL020',None)
		try:
			reader = csv.reader(file(table))
			tableResult = list(reader)
			self.result = tableResult
		except:
			error.errorPrint('ESQL021',None)
        	if sql != None:
			error.errorPrint('ESQL022',(table, sql))
		if master != None:
               		if masterlink == "" or detaillink == "":
				error.errorPrint('ESQL005',None)
			try:
				reader = csv.reader(file(master))
				masterResult = list(reader)
			except:
				error.errorPrint('ESQL023',None)
			# combine tableResult and masterResult depending on masterlink=detaillink
			result = self._combine(tableResult,masterResult,masterlink,detaillink)
			# sort result on sort-column
			if sort != None :
            			self.result = self._sortResult(result, sort)
		self._fields()

	def _combine(self,table,master,masterlink,detaillink):
		# combines table and master like an SQL-join
		tableFieldNames = table[0]
		masterFieldNames = master[0]
		try:
			detailIndex = tableFieldNames.index(detaillink)
		except ValueError:
			error.errorPrint('ESQL024',None)
		try:
			masterIndex = masterFieldNames.index(masterlink)
		except ValueError:
			error.errorPrint('ESQL025',None)
		master_help = {}
		for i in master[1:]: # cut first row
			master_help[i[masterIndex]] = i[:masterIndex] + i[masterIndex+1:]
		fieldNames = tableFieldNames + masterFieldNames[0:masterIndex] + masterFieldNames[masterIndex+1:]
		t = table[1:]
		result_table = []
		for i in table[1:]: # cut first row
			result_table.append(i + master_help[i[detailIndex]])
		res = []
		res.append(fieldNames)
		res = res + result_table
		return res

	def _sortcmp(self,a,b):
		return cmp(a[0],b[0])

	def _sortResult(self, result,sortfield):
		# sort list on sortfield
		try:
			sortIndex = result[0].index(sortfield)
		except ValueError:
			error.errorPrint('ESQL027',None)
		res = result[0]	# cut first row (fieldnames)
		rest = result[1:]
		restList= []
		for r in rest:	# restList[0] = sortitem
			r.insert(0,r[sortIndex])
			restList.append(r)
		restList.sort(self._sortcmp)  # sort list with _sortcmp
		resultat = []
		resultat.append(res)	# add fieldnames
		for r in restList:
			r1 = r[1:]	# deletes first column
			resultat.append(r1)
		return resultat


	def _length(self,feld,index):
		# returns greatest length of feld[index]
		max = 0
		for i in feld:
			if len(i) > max:
				max = len(i)
		return max

	def _fields(self):
		''' returns [(columnname,type),(columname,type) ...]
		'''
		self.felder = self.result[0]
		self.num_rows = len(self.result) - 1
		self.row_number = 0
		r_felder = {}
		l_felder = {}
		k = 0
		for feld in self.felder:
			a = None
			b=None
			try:
				a= string.atoi(self.result[1][k]) # test on integer
			except:
				pass
			try:
				b = string.atof(self.result[1][k]) #test on float
			except:
				pass
			if a != None:
				typ = "SHORT"
			elif b != None:
				typ = "FLOAT"
			else:
				typ = "VARCHAR"
			r_felder[feld] = typ
			l_felder[feld] = self._length(self.result,k)
			k +=1
		self.fields = r_felder
		self.field_len = l_felder


	def get_error(self):
		''' Returns Error Message
		'''
		pass

	def get_fields(self):
		''' returns Dictionary of TableColumnName:TableColumnType
		'''
		return self.fields

	def get_fieldlen(self):
		''' returns Dictionary of TableColumnName:TableColumnSize
		'''
		return self.field_len


	def get_numrows(self):
		''' returns number of rows of select
		'''
		return int(self.num_rows)

	def next_row(self):
		''' fetches the next row of resultset and returns an array of values
		'''
		self.row_number +=1
		if self.row_number > self.get_numrows():
			return None
		res = self.result[self.row_number]
		result = {}
		index = 0
		for i in self.felder:  # build dictionary columnName:value
			result[i]=res[index]
			index = index + 1
		return result

class Postgresql(DB):
	field_types = { "numeric":"DECIMAL","int2":"TINY","int4":"SHORT","integer":"SHORT","bigint":"LONG","int8":"LONG",
			"real":"FLOAT","double precision":"DOUBLE",6:"NULL",10:"DATE",11:"TIME",12:"DATETIME",
			"bpchar":"CHAR","varchar":"VARCHAR"}

	felder = []
	def __init__(self, host, user,passwd,db,table= None,sql=None, sort = None,master = None,
			masterlink = "", detaillink = ""):
	   	''' Connection to Mysql database (Paramters: host, user, passwd, db)
		returns Object or None. If None ist returned, an error occurred, which can be read with get_error()
		'''
		try:
			import pgdb
		except:
			error.errorPrint('ESQL040',None)
		self.db_handle = None
		import pgdb
        	try:
			self.db_handle = pgdb.connect(host=host, user=user, password = passwd, database =db)
        	except :
			error.errorPrint('ESQL041',(host, user, db))

		if self.db_handle == None:
			error.errorPrint('ESQL003',None)
        	if table != None and sql != None:
			error.errorPrint('ESQL004',None)
		if table != None:
			sqlCommand = "select * from %s " % (table)
			if master != None:
                		if masterlink == "" or detaillink == "":
					error.errorPrint('ESQL005',None)
				sqlCommandAdd = ",%s where %s.%s = %s.%s " %  (master, master, masterlink, table, detaillink)
				#sqlCommandAdd = ","
				sqlCommand = sqlCommand + sqlCommandAdd
			if sort != None :
            			sqlCommand = sqlCommand + " order by %s.%s" % (table, sort)
			self._select(sqlCommand)
			self._fields()

        	elif sql != None:
            		self._select(sql)
            		self._fields()
		self._tables(host,user,passwd,db)




	def _select(self, select_command):
		''' executes the select_command on the given database handle
		    returns true if ok, false otherwise
		'''

		try:
			self.cursor = self.db_handle.cursor()
			self.cursor.execute(select_command)
		except pgdb.Error:
			error.errorPrint('ESQL006',(select_command))

	def _fields(self):
		''' returns [(columnname,type),(columname,type) ...]
		'''

		self.felder = self.cursor.description
		r_felder = {}
		l_felder = {}
		for feld in self.felder:
			#f = (feld[0],self.field_types[feld[1]])
			r_felder[feld[0]] = self.field_types[feld[1]]
			l_felder[feld[0]] = feld[3]
		self.fields = r_felder
		self.field_len = l_felder

	def _tables(self,host,user,password,database):
		''' builds self.tables: Array of tableNames
		'''
		import pg
		conn = pg.DB(host=host,user=user)
		help_tables = conn.get_tables()
		self.tables = []
		for i in help_tables:
			if i[0:4] != "pga_":
			      self.tables.append(i)


	def get_error(self):
		''' Returns Error Message
		'''
		return self.Error

	def get_fields(self):
		''' returns Dictionary of TableColumnName:TableColumnType
		'''
		return self.fields

	def get_tables(self):
		''' returns Array of tableNames
		'''
		return self.tables

	def get_fieldlen(self):
		''' returns Dictionary of TableColumnName:TableColumnSize
		'''
		return self.field_len

	def get_numrows(self):
		''' returns number of rows of select
		'''
		return int(self.cursor.rowcount)

	def next_row(self):
		''' fetches the next row of resultset and returns an array of values
		'''
		res = self.cursor.fetchone()
		if res == None:  # last record fetched
			self.db_handle.close()
			return None
		result = {}
		index = 0
		field_names= []
		for f in self.felder:
			field_names.append(f[0])
		for i in field_names:  # build dictionary columnName:value
			result[i]=res[index]
			index = index + 1
		return result

def run_mysql():
# test of mysql database schorg with table addresses
	print "!!!!!!!!!!!!!!!!!!"
	print "! Run Mysql Test !"
	print "!!!!!!!!!!!!!!!!!!"
	print

	db = MySQL(host="localhost", user="schorg", passwd="schorg", db="schorg", table="addresses" ,sort="state",
		master= "state", masterlink="state", detaillink="state" )
	print "nach connect %s"% db

	felder = db.get_fields()
	feld_name =  felder.keys()
	print
	for i in feld_name:
		print i + " vom Typ " + felder[i]
	numrows = db.get_numrows()
	print "Rows=", numrows # test numrows
	print "name\t\tcity    state"
	print "==============================="
	for x in range(0,numrows):
		record = db.next_row()
		print record["name"],"\t\t",record["city"], record["state"]


def run_csv():
	# test csv
	print "!!!!!!!!!!!!!!!!"
	print "! Run CSV Test !"
	print "!!!!!!!!!!!!!!!!"
	print

	cv = CSV(table="numbers.csv",master="typen.csv",detaillink="id",masterlink="id",sort="id")
	felder = cv.get_fields()

	feld_name =  felder.keys()
	print felder,feld_name
	print
	for i in feld_name:
		print i + " vom Typ " + felder[i]
	numrows = cv.get_numrows()
	print "Rows=", numrows # test numrows
	row = cv.next_row
	for i in feld_name:
		print i,"\t",
	print
	print "==============================="
	for x in range(0,numrows):
		record = cv.next_row()
		for i in range(0,len(feld_name)):
			print record[feld_name[i]],"\t",
		print
def run_pgsql():
# test of mysql database schorg with table addresses
	print "!!!!!!!!!!!!!!!!!!!!!!!"
	print "! Run Postgresql Test !"
	print "!!!!!!!!!!!!!!!!!!!!!!!"
	print
	db = Postgresql(host="", user="schorg", passwd="schorg", db="schorg", sql= "select * from addresses" )
	print "nach connect %s"% db
	tables = db.get_tables()
	print "Tables in Database"
	print "--------------------"
	for i in tables:
		print "   table:",i
	print "____________________________________"
	print
	print "Fields of output"
	print
	felder = db.get_fields()
	feld_name =  felder.keys()
	print
	for i in feld_name:
		print i + " vom Typ " + felder[i]
	numrows = db.get_numrows()
	print
	print "Rows=", numrows # test numrows
	print
	for i in feld_name:
		print i,"\t",
	print
	print "==============================="
	for x in range(0,numrows):
		record = db.next_row()
		for i in range(0,len(feld_name)):
			print record[feld_name[i]],"\t",
		print

def run():
	''' Only for test purposes '''
	#run_mysql()
	run_csv()
	#run_pgsql()
if __name__ == "__main__":
        run()
